/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cz.tpj.database.obrazok_mesto;

import cz.tpj.database.DatabaseConnector;
import cz.tpj.database.obrazky.Obrazok;
import cz.tpj.database.obrazky.Table_picture;
import cz.tpj.util.ErrorDatabase;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Trieda sprostredkuvava metody pre vyberanie Obrazkov mesta z databazy
 * @author jabo
 */

public class Table_CityPictures {
    //------------------finalni promenne----------------------------------------
    public static final String TABLE       = "obrazok_mesto";
    public static final String ID          = "id";
    public static final String PICTURES_ID = "obrazky_id";
    public static final String CITY_ID     = "mesto_id";

    /*
     *CREATE TABLE `obrazok_mesto` (
  `id` mediumint(9) NOT NULL,
  `obrazky_id` mediumint(9) default NULL,
  `mesto_id` smallint(6) default NULL,

     */
    //
    private static Table_CityPictures im = null;
    private Connection con;
    private Statement stmt = null;
    //-------------------------------------------------------------------------
    //======================konstruktor================
    private Table_CityPictures(){}

    /**
     * Vrací jedinou instanci této třídy.
     * @return Table_city
     */
    
    public static Table_CityPictures getInstance(){
        if(im == null){
            im = new Table_CityPictures();
        }
        return im;
    }
    //=======================metody-------------------------
    private void databaseConnect() throws SQLException{
        DatabaseConnector dc = DatabaseConnector.getInstance();
        con = dc.getConnection();
        stmt = con.createStatement();
    }
    //--------------------------------------------------------------------------
    /**
     * vrati obrazky zadaneho mesta, ktore je definovane podla Id
     * @return ArrayList<Obrazok>
     */
    public ArrayList<Obrazok> getPictureFromCityId(String cityId){
        ResultSet rs = null;
        ArrayList<Obrazok> score = new ArrayList<Obrazok>();
        try {
            databaseConnect();
            //SELECT a.* FROM obrazky a LEFT JOIN (obrazok_mesto b, mesto c) ON (a.id = b.obrazky_id AND b.mesto_id = c.id) WHERE c.id = 2;
            //rs = stmt.executeQuery("SELECT * FROM "+Table_picture.TABLE+";");
            rs = stmt.executeQuery("SELECT a.* FROM obrazky a " +
                    "LEFT JOIN (obrazok_mesto b, mesto c) " +
                    "ON (a.id = b.obrazky_id AND b.mesto_id = c.id) WHERE c.id = "+cityId+";");

            String id = "hhh";
            String path = "hhh";
            Obrazok obr;
            while(rs.next()){
                id = rs.getString(Table_picture.ID);
                path = rs.getString(Table_picture.PATH);
                obr = new Obrazok(id, path);
                score.add(obr);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Table_CityPictures.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                rs.close();
                stmt.close();
            } catch (Exception ex) {
            }
        }
        return score;
    }
    
    /**
     * vlozi obrazok k mestu
     * @param cityID
     * @param imageID
     * @throws ErrorDatabase
     */
	public boolean addPictureToCity(String cityID, String imageID) throws ErrorDatabase{
        boolean ok = false;
        
       	//mesto neexistuje, vkladaj ho tam
        try {
            databaseConnect();                       
            String s = "INSERT INTO " + TABLE + "" +
                    "(" + PICTURES_ID + "," + CITY_ID + ")" +
                    "VALUES ('" + imageID + "','" + cityID + "')";

            int updated = stmt.executeUpdate(s);
            //pokud
            //1) byl zmenen jeden radek, pak potvrzeni transakce
            //2) bylo zmeneno vice a nebo zadny, pak vratit transakci zpet
            if(updated == 1){
                con.commit(); //potvrzeni transakce
                ok = true;
            }else{
                con.rollback(); //vraceni transakce
                ok = false;
            }
        } catch (SQLException ex) {
            String s = ex.getMessage();
            throw new ErrorDatabase(ErrorDatabase.ERROR_DATABASE_CONECTION, "Cbyba pri praci s databazovym serverem." + s);
        } finally{
            try {
                stmt.close();
            } catch (SQLException ex) {}
        }
        return ok;
		
		
	}
}
